if exists (select * from sysobjects where id = object_id('dbo.Employee Sales by Country') and sysstat & 0xf = 4)
	drop procedure [dbo].[Employee Sales by Country]
GO
if exists (select * from sysobjects where id = object_id('dbo.Sales by Year') and sysstat & 0xf = 4)
	drop procedure [dbo].[Sales by Year]
GO
if exists (select * from sysobjects where id = object_id('dbo.Ten Most Expensive Products') and sysstat & 0xf = 4)
	drop procedure [dbo].[Ten Most Expensive Products]
GO
if exists (select * from sysobjects where id = object_id('dbo.Category Sales for 1997') and sysstat & 0xf = 2)
	drop view [dbo].[Category Sales for 1997]
GO
if exists (select * from sysobjects where id = object_id('dbo.Sales by Category') and sysstat & 0xf = 2)
	drop view [dbo].[Sales by Category]
GO
if exists (select * from sysobjects where id = object_id('dbo.Sales Totals by Amount') and sysstat & 0xf = 2)
	drop view [dbo].[Sales Totals by Amount]
GO
if exists (select * from sysobjects where id = object_id('dbo.Summary of Sales by Quarter') and sysstat & 0xf = 2)
	drop view [dbo].[Summary of Sales by Quarter]
GO
if exists (select * from sysobjects where id = object_id('dbo.Summary of Sales by Year') and sysstat & 0xf = 2)
	drop view [dbo].[Summary of Sales by Year]
GO
if exists (select * from sysobjects where id = object_id('dbo.Invoices') and sysstat & 0xf = 2)
	drop view [dbo].[Invoices]
GO
if exists (select * from sysobjects where id = object_id('dbo.Order Details Extended') and sysstat & 0xf = 2)
	drop view [dbo].[Order Details Extended]
GO
if exists (select * from sysobjects where id = object_id('dbo.Order Subtotals') and sysstat & 0xf = 2)
	drop view [dbo].[Order Subtotals]
GO
if exists (select * from sysobjects where id = object_id('dbo.Product Sales for 1997') and sysstat & 0xf = 2)
	drop view [dbo].[Product Sales for 1997]
GO
if exists (select * from sysobjects where id = object_id('dbo.Alphabetical list of products') and sysstat & 0xf = 2)
	drop view [dbo].[Alphabetical list of products]
GO
if exists (select * from sysobjects where id = object_id('dbo.Current Product List') and sysstat & 0xf = 2)
	drop view [dbo].[Current Product List]
GO
if exists (select * from sysobjects where id = object_id('dbo.Orders Qry') and sysstat & 0xf = 2)
	drop view [dbo].[Orders Qry]
GO
if exists (select * from sysobjects where id = object_id('dbo.Products Above Average Price') and sysstat & 0xf = 2)
	drop view [dbo].[Products Above Average Price]
GO
if exists (select * from sysobjects where id = object_id('dbo.Products by Category') and sysstat & 0xf = 2)
	drop view [dbo].[Products by Category]
GO
if exists (select * from sysobjects where id = object_id('dbo.Quarterly Orders') and sysstat & 0xf = 2)
	drop view [dbo].[Quarterly Orders]
GO
if exists (select * from sysobjects where id = object_id('dbo.Customer and Suppliers by City') and sysstat & 0xf = 2)
	drop view [dbo].[Customer and Suppliers by City]
GO
if exists (select * from sysobjects where id = object_id('dbo.Order Details') and sysstat & 0xf = 3)
	drop table [dbo].[Order Details]
GO
if exists (select * from sysobjects where id = object_id('dbo.Orders') and sysstat & 0xf = 3)
	drop table [dbo].[Orders]
GO
if exists (select * from sysobjects where id = object_id('dbo.Products') and sysstat & 0xf = 3)
	drop table [dbo].[Products]
GO
if exists (select * from sysobjects where id = object_id('dbo.Categories') and sysstat & 0xf = 3)
	drop table [dbo].[Categories]
GO
if exists (select * from sysobjects where id = object_id('dbo.CustomerCustomerDemo') and sysstat & 0xf = 3)
	drop table [dbo].[CustomerCustomerDemo]
GO
if exists (select * from sysobjects where id = object_id('dbo.Customers') and sysstat & 0xf = 3)
	drop table [dbo].[Customers]
GO
if exists (select * from sysobjects where id = object_id('dbo.CustomerDemographics') and sysstat & 0xf = 3)
	drop table [dbo].[CustomerDemographics]
GO
if exists (select * from sysobjects where id = object_id('dbo.Shippers') and sysstat & 0xf = 3)
	drop table [dbo].[Shippers]
GO
if exists (select * from sysobjects where id = object_id('dbo.Suppliers') and sysstat & 0xf = 3)
	drop table [dbo].[Suppliers]
GO
if exists (select * from sysobjects where id = object_id('dbo.EmployeeTerritories') and sysstat & 0xf = 3)
	drop table [dbo].[EmployeeTerritories]
GO
if exists (select * from sysobjects where id = object_id('dbo.Employees') and sysstat & 0xf = 3)
	drop table [dbo].[Employees]
GO
if exists (select * from sysobjects where id = object_id('dbo.Territories') and sysstat & 0xf = 3)
	drop table [dbo].[Territories]
GO
if exists (select * from sysobjects where id = object_id('dbo.Region') and sysstat & 0xf = 3)
	drop table [dbo].[Region]
GO
CREATE TABLE [Employees] (
	[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
	[LastName] nvarchar (20) NOT NULL ,
	[FirstName] nvarchar (10) NOT NULL ,
	[Title] nvarchar (30) NULL ,
	[TitleOfCourtesy] nvarchar (25) NULL ,
	[BirthDate] [datetime] NULL ,
	[HireDate] [datetime] NULL ,
	[Address] nvarchar (60) NULL ,
	[City] nvarchar (15) NULL ,
	[Region] nvarchar (15) NULL ,
	[PostalCode] nvarchar (10) NULL ,
	[Country] nvarchar (15) NULL ,
	[HomePhone] nvarchar (24) NULL ,
	[Extension] nvarchar (4) NULL ,
	[Photo] [image] NULL ,
	[Notes] [ntext] NULL ,
	[ReportsTo] [int] NULL ,
	[PhotoPath] nvarchar (255) NULL ,
	CONSTRAINT [PK_Employees] PRIMARY KEY  CLUSTERED 
	(
		[EmployeeID]
	),
	CONSTRAINT [FK_Employees_Employees] FOREIGN KEY 
	(
		[ReportsTo]
	) REFERENCES [dbo].[Employees] (
		[EmployeeID]
	),
	CONSTRAINT [CK_Birthdate] CHECK (BirthDate < getdate())
);
 CREATE  INDEX [LastName] ON [dbo].[Employees]([LastName]);
 CREATE  INDEX [PostalCode] ON [dbo].[Employees]([PostalCode]);

CREATE TABLE [Categories] (
	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryName] nvarchar (15) NOT NULL ,
	[Description] [ntext] NULL ,
	[Picture] [image] NULL ,
	CONSTRAINT [PK_Categories] PRIMARY KEY  CLUSTERED 
	(
		[CategoryID]
	)
);
 CREATE  INDEX [CategoryName] ON [dbo].[Categories]([CategoryName]);

CREATE TABLE [Customers] (
	[CustomerID] nchar (5) NOT NULL ,
	[CompanyName] nvarchar (40) NOT NULL ,
	[ContactName] nvarchar (30) NULL ,
	[ContactTitle] nvarchar (30) NULL ,
	[Address] nvarchar (60) NULL ,
	[City] nvarchar (15) NULL ,
	[Region] nvarchar (15) NULL ,
	[PostalCode] nvarchar (10) NULL ,
	[Country] nvarchar (15) NULL ,
	[Phone] nvarchar (24) NULL ,
	[Fax] nvarchar (24) NULL ,
	CONSTRAINT [PK_Customers] PRIMARY KEY  CLUSTERED 
	(
		[CustomerID]
	)
);
 CREATE  INDEX [City] ON [dbo].[Customers]([City]);
 CREATE  INDEX [CompanyName] ON [dbo].[Customers]([CompanyName]);
 CREATE  INDEX [PostalCode] ON [dbo].[Customers]([PostalCode]);
 CREATE  INDEX [Region] ON [dbo].[Customers]([Region]);

CREATE TABLE [Shippers] (
	[ShipperID] [int] IDENTITY (1, 1) NOT NULL ,
	[CompanyName] nvarchar (40) NOT NULL ,
	[Phone] nvarchar (24) NULL ,
	CONSTRAINT [PK_Shippers] PRIMARY KEY  CLUSTERED 
	(
		[ShipperID]
	)
);
CREATE TABLE [Suppliers] (
	[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
	[CompanyName] nvarchar (40) NOT NULL ,
	[ContactName] nvarchar (30) NULL ,
	[ContactTitle] nvarchar (30) NULL ,
	[Address] nvarchar (60) NULL ,
	[City] nvarchar (15) NULL ,
	[Region] nvarchar (15) NULL ,
	[PostalCode] nvarchar (10) NULL ,
	[Country] nvarchar (15) NULL ,
	[Phone] nvarchar (24) NULL ,
	[Fax] nvarchar (24) NULL ,
	[HomePage] [ntext] NULL ,
	CONSTRAINT [PK_Suppliers] PRIMARY KEY  CLUSTERED 
	(
		[SupplierID]
	)
);
 CREATE  INDEX [CompanyName] ON [dbo].[Suppliers]([CompanyName]);
 CREATE  INDEX [PostalCode] ON [dbo].[Suppliers]([PostalCode]);

CREATE TABLE [Orders] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustomerID] nchar (5) NULL ,
	[EmployeeID] [int] NULL ,
	[OrderDate] [datetime] NULL ,
	[RequiredDate] [datetime] NULL ,
	[ShippedDate] [datetime] NULL ,
	[ShipVia] [int] NULL ,
	[Freight] [money] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT (0),
	[ShipName] nvarchar (40) NULL ,
	[ShipAddress] nvarchar (60) NULL ,
	[ShipCity] nvarchar (15) NULL ,
	[ShipRegion] nvarchar (15) NULL ,
	[ShipPostalCode] nvarchar (10) NULL ,
	[ShipCountry] nvarchar (15) NULL ,
	CONSTRAINT [PK_Orders] PRIMARY KEY  CLUSTERED 
	(
		[OrderID]
	),
	CONSTRAINT [FK_Orders_Customers] FOREIGN KEY 
	(
		[CustomerID]
	) REFERENCES [dbo].[Customers] (
		[CustomerID]
	),
	CONSTRAINT [FK_Orders_Employees] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [dbo].[Employees] (
		[EmployeeID]
	),
	CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY 
	(
		[ShipVia]
	) REFERENCES [dbo].[Shippers] (
		[ShipperID]
	)
);
 CREATE  INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]);
 CREATE  INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]);
 CREATE  INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]);
 CREATE  INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]);
 CREATE  INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]);
 CREATE  INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]);
 CREATE  INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]);
 CREATE  INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]);

CREATE TABLE [Products] (
	[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
	[ProductName] nvarchar (40) NOT NULL ,
	[SupplierID] [int] NULL ,
	[CategoryID] [int] NULL ,
	[QuantityPerUnit] nvarchar (20) NULL ,
	[UnitPrice] [money] NULL CONSTRAINT [DF_Products_UnitPrice] DEFAULT (0),
	[UnitsInStock] [smallint] NULL CONSTRAINT [DF_Products_UnitsInStock] DEFAULT (0),
	[UnitsOnOrder] [smallint] NULL CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT (0),
	[ReorderLevel] [smallint] NULL CONSTRAINT [DF_Products_ReorderLevel] DEFAULT (0),
	[Discontinued] [bit] NOT NULL CONSTRAINT [DF_Products_Discontinued] DEFAULT (0),
	CONSTRAINT [PK_Products] PRIMARY KEY  CLUSTERED 
	(
		[ProductID]
	),
	CONSTRAINT [FK_Products_Categories] FOREIGN KEY 
	(
		[CategoryID]
	) REFERENCES [dbo].[Categories] (
		[CategoryID]
	),
	CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY 
	(
		[SupplierID]
	) REFERENCES [dbo].[Suppliers] (
		[SupplierID]
	),
	CONSTRAINT [CK_Products_UnitPrice] CHECK (UnitPrice >= 0),
	CONSTRAINT [CK_ReorderLevel] CHECK (ReorderLevel >= 0),
	CONSTRAINT [CK_UnitsInStock] CHECK (UnitsInStock >= 0),
	CONSTRAINT [CK_UnitsOnOrder] CHECK (UnitsOnOrder >= 0)
);
 CREATE  INDEX [CategoriesProducts] ON [dbo].[Products]([CategoryID]);
 CREATE  INDEX [CategoryID] ON [dbo].[Products]([CategoryID]);
 CREATE  INDEX [ProductName] ON [dbo].[Products]([ProductName]);
 CREATE  INDEX [SupplierID] ON [dbo].[Products]([SupplierID]);
 CREATE  INDEX [SuppliersProducts] ON [dbo].[Products]([SupplierID]);

CREATE TABLE [Order Details] (
	[OrderID] [int] NOT NULL ,
	[ProductID] [int] NOT NULL ,
	[UnitPrice] [money] NOT NULL CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT (0),
	[Quantity] [smallint] NOT NULL CONSTRAINT [DF_Order_Details_Quantity] DEFAULT (1),
	[Discount] [real] NOT NULL CONSTRAINT [DF_Order_Details_Discount] DEFAULT (0),
	CONSTRAINT [PK_Order_Details] PRIMARY KEY  CLUSTERED 
	(
		[OrderID],
		[ProductID]
	),
	CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY 
	(
		[OrderID]
	) REFERENCES [dbo].[Orders] (
		[OrderID]
	),
	CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY 
	(
		[ProductID]
	) REFERENCES [dbo].[Products] (
		[ProductID]
	),
	CONSTRAINT [CK_Discount] CHECK (Discount >= 0 and (Discount <= 1)),
	CONSTRAINT [CK_Quantity] CHECK (Quantity > 0),
	CONSTRAINT [CK_UnitPrice] CHECK (UnitPrice >= 0)
);
 CREATE  INDEX [OrderID] ON [dbo].[Order Details]([OrderID]);
 CREATE  INDEX [OrdersOrder_Details] ON [dbo].[Order Details]([OrderID]);
 CREATE  INDEX [ProductID] ON [dbo].[Order Details]([ProductID]);
 CREATE  INDEX [ProductsOrder_Details] ON [dbo].[Order Details]([ProductID]);
GO

create view [Customer and Suppliers by City] AS
SELECT City, CompanyName, ContactName, 'Customers' AS Relationship 
FROM Customers
UNION SELECT City, CompanyName, ContactName, 'Suppliers'
FROM Suppliers
--ORDER BY City, CompanyName
GO

create view [Alphabetical list of products] AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0));
GO

create view [Current Product List] AS
SELECT Product_List.ProductID, Product_List.ProductName
FROM Products AS Product_List
WHERE (((Product_List.Discontinued)=0))
--ORDER BY Product_List.ProductName
GO

create view [Orders Qry] AS
SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, 
	Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, 
	Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, 
	Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GO

create view [Products Above Average Price] AS
SELECT Products.ProductName, Products.UnitPrice
FROM Products
WHERE Products.UnitPrice>(SELECT AVG(UnitPrice) From Products)
--ORDER BY Products.UnitPrice DESC
GO

create view [Products by Category] AS
SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued <> 1
--ORDER BY Categories.CategoryName, Products.ProductName
GO

create view [Quarterly Orders] AS
SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'
GO

create view Invoices AS
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, 
	Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City, 
	Customers.Region, Customers.PostalCode, Customers.Country, 
	(FirstName + ' ' + LastName) AS Salesperson, 
	Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, 
	[Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, 
	[Order Details].Discount, 
	(CONVERT(money,([Order Details].UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
FROM 	Shippers INNER JOIN 
		(Products INNER JOIN 
			(
				(Employees INNER JOIN 
					(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 
				ON Employees.EmployeeID = Orders.EmployeeID) 
			INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) 
		ON Products.ProductID = [Order Details].ProductID) 
	ON Shippers.ShipperID = Orders.ShipVia
GO

create view [Order Details Extended] AS
SELECT [Order Details].OrderID, [Order Details].ProductID, Products.ProductName, 
	[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, 
	(CONVERT(money,([Order Details].UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
--ORDER BY [Order Details].OrderID
GO

create view [Order Subtotals] AS
SELECT [Order Details].OrderID, Sum(CONVERT(money,([Order Details].UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal
FROM [Order Details]
GROUP BY [Order Details].OrderID
GO

create view [Product Sales for 1997] AS
SELECT Categories.CategoryName, Products.ProductName, 
Sum(CONVERT(money,([Order Details].UnitPrice*Quantity*(1-Discount)/100))*100) AS ProductSales
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) 
	INNER JOIN (Orders 
		INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) 
	ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.ShippedDate) Between '19970101' And '19971231'))
GROUP BY Categories.CategoryName, Products.ProductName
GO

create view [Category Sales for 1997] AS
SELECT [Product Sales for 1997].CategoryName, Sum([Product Sales for 1997].ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY [Product Sales for 1997].CategoryName
GO

create view [Sales by Category] AS
SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName, 
	Sum([Order Details Extended].ExtendedPrice) AS ProductSales
FROM 	Categories INNER JOIN 
		(Products INNER JOIN 
			(Orders INNER JOIN [Order Details Extended] ON Orders.OrderID = [Order Details Extended].OrderID) 
		ON Products.ProductID = [Order Details Extended].ProductID) 
	ON Categories.CategoryID = Products.CategoryID
WHERE Orders.OrderDate BETWEEN '19970101' And '19971231'
GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName
--ORDER BY Products.ProductName
GO

create view [Sales Totals by Amount] AS
SELECT [Order Subtotals].Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate
FROM 	Customers INNER JOIN 
		(Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID) 
	ON Customers.CustomerID = Orders.CustomerID
WHERE ([Order Subtotals].Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231');
GO

create view [Summary of Sales by Quarter] AS
SELECT Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate
GO

create view [Summary of Sales by Year] AS
SELECT Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID
WHERE Orders.ShippedDate IS NOT NULL
--ORDER BY Orders.ShippedDate
GO

create procedure [Ten Most Expensive Products] AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
GO

create procedure [Employee Sales by Country] 
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal AS SaleAmount
FROM Employees INNER JOIN 
	(Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID) 
	ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
GO

create procedure [Sales by Year] 
	@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
GO

if exists (select * from sysobjects where id = object_id('dbo.CustOrdersDetail'))
    drop procedure dbo.CustOrdersDetail
GO

CREATE PROCEDURE CustOrdersDetail @OrderID int
AS
SELECT ProductName,
    UnitPrice=ROUND(Od.UnitPrice, 2),
    Quantity,
    Discount=CONVERT(int, Discount * 100), 
    ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID
GO


if exists (select * from sysobjects where id = object_id('dbo.CustOrdersOrders'))
	drop procedure dbo.CustOrdersOrders
GO

CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
AS
SELECT OrderID, 
	OrderDate,
	RequiredDate,
	ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID
GO


if exists (select * from sysobjects where id = object_id('dbo.CustOrderHist') and sysstat & 0xf = 4)
	drop procedure dbo.CustOrderHist
GO
CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
GO

if exists (select * from sysobjects where id = object_id('dbo.SalesByCategory') and sysstat & 0xf = 4)
	drop procedure dbo.SalesByCategory
GO
CREATE PROCEDURE SalesByCategory
    @CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998' 
BEGIN
	SELECT @OrdYear = '1998'
END

SELECT ProductName,
	TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID 
	AND OD.ProductID = P.ProductID 
	AND P.CategoryID = C.CategoryID
	AND C.CategoryName = @CategoryName
	AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
GO


/* The follwing adds tables to the Northwind database */


CREATE TABLE [dbo].[CustomerCustomerDemo] 
	([CustomerID] nchar (5) NOT NULL,
	[CustomerTypeID] [nchar] (10) NOT NULL
) ON [PRIMARY] 
GO

CREATE TABLE [dbo].[CustomerDemographics] 
	([CustomerTypeID] [nchar] (10) NOT NULL ,
	[CustomerDesc] [ntext] NULL 
)  ON [PRIMARY] 
;		
	
CREATE TABLE [dbo].[Region] 
	( [RegionID] [int] NOT NULL ,
	[RegionDescription] [nchar] (50) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Territories] 
	([TerritoryID] [nvarchar] (20) NOT NULL ,
	[TerritoryDescription] [nchar] (50) NOT NULL ,
        [RegionID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EmployeeTerritories] 
	([EmployeeID] [int] NOT NULL,
	[TerritoryID] [nvarchar] (20) NOT NULL 
) ON [PRIMARY]


ALTER TABLE CustomerCustomerDemo
	ADD CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY  NONCLUSTERED 
	(
		[CustomerID],
		[CustomerTypeID]
	) ON [PRIMARY]
GO

ALTER TABLE CustomerDemographics
	ADD CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY  NONCLUSTERED 
	(
		[CustomerTypeID]
	) ON [PRIMARY]
GO

ALTER TABLE CustomerCustomerDemo
	ADD CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY 
	(
		[CustomerTypeID]
	) REFERENCES [dbo].[CustomerDemographics] (
		[CustomerTypeID]
	);

ALTER TABLE CustomerCustomerDemo
	ADD CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY
	(
		[CustomerID]
	) REFERENCES [dbo].[Customers] (
		[CustomerID]
	);

ALTER TABLE Region
	ADD CONSTRAINT [PK_Region] PRIMARY KEY  NONCLUSTERED 
	(
		[RegionID]
	)  ON [PRIMARY] 
GO

ALTER TABLE Territories
	ADD CONSTRAINT [PK_Territories] PRIMARY KEY  NONCLUSTERED 
	(
		[TerritoryID]
	)  ON [PRIMARY] 
GO

ALTER TABLE Territories
	ADD CONSTRAINT [FK_Territories_Region] FOREIGN KEY 
	(
		[RegionID]
	) REFERENCES [dbo].[Region] (
		[RegionID]
	);
GO

ALTER TABLE EmployeeTerritories
	ADD CONSTRAINT [PK_EmployeeTerritories] PRIMARY KEY  NONCLUSTERED 
	(
		[EmployeeID],
		[TerritoryID]
	);
GO

ALTER TABLE EmployeeTerritories
	ADD CONSTRAINT [FK_EmployeeTerritories_Employees] FOREIGN KEY 
	(
		[EmployeeID]
	) REFERENCES [dbo].[Employees] (
		[EmployeeID]
	);
GO

ALTER TABLE EmployeeTerritories	
	ADD CONSTRAINT [FK_EmployeeTerritories_Territories] FOREIGN KEY 
	(
		[TerritoryID]
	) REFERENCES [dbo].[Territories] (
		[TerritoryID]
	);
GO

--populate the Categories table
INSERT INTO [Categories] (
  [CategoryName],  [Description],  [Picture]) VALUES (
'Beverages' ,'Soft drinks, coffees, teas, beers, and ales' ,NULL
);

INSERT INTO [Categories] (
  [CategoryName],  [Description],  [Picture]) VALUES (
'Condiments' ,'Sweet and savory sauces, relishes, spreads, and seasonings' ,NULL
);

INSERT INTO [Categories] (
  [CategoryName],  [Description],  [Picture]) VALUES (
'Confections' ,'Desserts, candies, and sweet breads' ,NULL
);

INSERT INTO [Categories] (
  [CategoryName],  [Description],  [Picture]) VALUES (
'Dairy Products' ,'Cheeses' ,NULL
);

INSERT INTO [Categories] (
  [CategoryName],  [Description],  [Picture]) VALUES (
'Grains/Cereals' ,'Breads, crackers, pasta, and cereal' ,NULL
);

INSERT INTO [Categories] (
  [CategoryName],  [Description],  [Picture]) VALUES (
'Meat/Poultry' ,'Prepared meats' ,NULL
);

INSERT INTO [Categories] (
  [CategoryName],  [Description],  [Picture]) VALUES (
'Produce' ,'Dried fruit and bean curd' ,NULL
);

INSERT INTO [Categories] (
  [CategoryName],  [Description],  [Picture]) VALUES (
'Seafood' ,'Seaweed and fish' ,NULL
);